﻿using System;
using System.Data;
using System.Data.Common;

namespace DAL
{
    internal sealed class SqlHelper
    {
        #region private utility methods & constructors

        // 屏蔽构造函数
        private SqlHelper() { }

        private static void AttachParameters(DbCommand command, DbParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (var p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }
        #endregion

        #region PrepareParameter

        public static DbParameter PrepareParameter(string providerName, string parameterName, DbType dbType, object value)
        {
            return PrepareParameter(DbProviderFactories.GetFactory(providerName), parameterName, dbType, value);
        }

        public static DbParameter PrepareParameter(string providerName, string parameterName, DbType dbType, object value, ParameterDirection parameterDirection)
        {
            return PrepareParameter(DbProviderFactories.GetFactory(providerName), parameterName, dbType, value, parameterDirection);
        }

        /// <summary>
        /// Prepare Parameter
        /// </summary>
        /// <param name="parameterName">Parameter Name</param>
        /// <param name="dbType">SqlDbType</param>
        /// <param name="value">value</param>
        /// <returns>DbParameter object</returns>
        public static DbParameter PrepareParameter(DbProviderFactory dbFactory, string parameterName, DbType dbType, object value)
        {
            DbParameter dbParameter = dbFactory.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.DbType = dbType;
            dbParameter.Value = value;
            return dbParameter;
        }

        /// <summary>
        /// Prepare Parameter
        /// </summary>
        /// <param name="parameterName">Parameter Name</param>
        /// <param name="dbType">SqlDbType</param>
        /// <param name="value">value</param>
        /// <returns>DbParameter object</returns>
        /// <param name="parameterDirection">ParameterDirection</param>
        public static DbParameter PrepareParameter(DbProviderFactory dbFactory, string parameterName, DbType dbType, object value, ParameterDirection parameterDirection)
        {
            DbParameter dbParameter = dbFactory.CreateParameter();
            dbParameter.ParameterName = parameterName;
            dbParameter.DbType = dbType;
            dbParameter.Value = value;
            dbParameter.Direction = parameterDirection;
            return dbParameter;
        }
        #endregion PrepareParameter

        #region ExecuteReader
        public static DbDataReader ExecuteReader(string providerName, string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteReader(DbProviderFactories.GetFactory(providerName), connectionString, commandType, commandText, new DbParameter[0]);
        }

        public static DbDataReader ExecuteReader(string providerName, string connectionString, CommandType commandType, string commandText, params DbParameter[] commandParameters)
        {
            return ExecuteReader(DbProviderFactories.GetFactory(providerName), connectionString, commandType, commandText, commandParameters);
        }

        public static DbDataReader ExecuteReader(DbProviderFactory dbFactory, string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteReader(dbFactory, connectionString, commandType, commandText, new DbParameter[0]);
        }

        public static DbDataReader ExecuteReader(DbProviderFactory dbFactory, string connectionString, CommandType commandType, string commandText, params DbParameter[] commandParameters)
        {
            DbConnection conn = dbFactory.CreateConnection();
            conn.ConnectionString = connectionString;

            DbCommand cmd = dbFactory.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandParameters);
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        #endregion

        #region ExecuteNonQuery
        public static int ExecuteNonQuery(string providerName, string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(DbProviderFactories.GetFactory(providerName), connectionString, commandType, commandText, new DbParameter[0]);
        }

        public static int ExecuteNonQuery(string providerName, string connectionString, CommandType commandType, string commandText, params DbParameter[] commandParameters)
        {
            return ExecuteNonQuery(DbProviderFactories.GetFactory(providerName), connectionString, commandType, commandText, commandParameters);
        }

        public static int ExecuteNonQuery(DbProviderFactory dbFactory, string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(dbFactory, connectionString, commandType, commandText, new DbParameter[0]);
        }

        public static int ExecuteNonQuery(DbProviderFactory dbFactory, string connectionString, CommandType commandType, string commandText, params DbParameter[] commandParameters)
        {
            DbConnection conn = dbFactory.CreateConnection();
            conn.ConnectionString = connectionString;

            DbCommand cmd = dbFactory.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandParameters);

            conn.Open();
            int retval = cmd.ExecuteNonQuery();
            conn.Close();
            return retval;
        }
        #endregion

        #region ExecuteDataSet
        public static DataSet ExecuteDataSet(string providerName, string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataSet(DbProviderFactories.GetFactory(providerName), connectionString, commandType, commandText, new DbParameter[0]);
        }

        public static DataSet ExecuteDataSet(string providerName, string connectionString, CommandType commandType, string commandText, params DbParameter[] commandParameters)
        {
            return ExecuteDataSet(DbProviderFactories.GetFactory(providerName), connectionString, commandType, commandText, commandParameters);
        }

        public static DataSet ExecuteDataSet(DbProviderFactory dbFactory, string connectionString, CommandType commandType, string commandText)
        {
            return ExecuteDataSet(dbFactory, connectionString, commandType, commandText, new DbParameter[0]);
        }

        public static DataSet ExecuteDataSet(DbProviderFactory dbFactory, string connectionString, CommandType commandType, string commandText, params DbParameter[] commandParameters)
        {
            DbConnection conn = dbFactory.CreateConnection();
            conn.ConnectionString = connectionString;

            DbCommand cmd = dbFactory.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandParameters);

            DbDataAdapter da = dbFactory.CreateDataAdapter();
            da.SelectCommand = cmd;

            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        #endregion
    }
}
